/**
 * 
 */
package user_client;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import oracle.jdbc.internal.ClientDataSupport;

import database_objects.Department;

import library.DepartmentManagerPane;

/**
 * 
 * @author Pawel Rychly
 *
 */
public abstract class DataBaseClient {
	
	/**
	 * Represents connection to database
	 */
	private Connection connection;
	private String password = null;
	private String userName = null;
	
	public DataBaseClient(Connection connection, String userName, String password) {
		this.connection = connection;
		this.userName = userName;
		this.password = password;
	}
	
	//returns informations about user credibility
	private boolean checkUserCredibility(){
		//TODO: definition of this function 
		return true;
	}
	
	/**
	 * Function finds a book in database;
	 */
	public void searchBook(){
		//TODO: definition of this function
	}
	
	/**
	 * Function returns a Connection object.
	 * @return
	 */
	public Connection getConnection() {
		return connection;
	}
	
	/**
	 * Returns a password of this client.
	 * @return password.
	 */
	public String getPassword() {
		return password;
	}
	
	/**
	 * Returns name of this client.
	 * @return name.
	 */
	public String getUserName() {
		return userName;
	}
	
	
	private ResultSet simpleSearchValues(ArrayList<String>  cols,
			ArrayList<String> values,
			String nameOfTable) throws SQLException {
		
		String whereCondition = ""; 
		for (Integer i = 0; i < cols.size(); i++) {
			if (i < values.size() && (values.get(i) != null)) {
				if (i > 0) {
					whereCondition += " and ";
				}
				String value = values.get(i);
				whereCondition += ("( lower(substr(" + cols.get(i) +
						", 0, " + value.length() + ")) = lower('" + value + "' ))" );
				
			}
		}
		if (whereCondition.length() > 0) {
			whereCondition = " where " + whereCondition;
		}
		System.out.println("where condition:");
		System.out.println(whereCondition);
		
		
		Statement stmt = this.connection.createStatement();
		ResultSet res = stmt
				.executeQuery("select * from " + nameOfTable + whereCondition );
		
		
		return res;
	}
	
	
	public ArrayList<Department> searchDepartments(
			String nameValue, String numValue) throws SQLException{
		ArrayList<String> rows = new ArrayList<String>();
				
		ArrayList<String> values = new ArrayList<String>();
		if (nameValue.length() > 0) {
			rows.add("nazwa");
			values.add(nameValue);
		}
		
		if (numValue.length() > 0) {
			rows.add("nr_regalu");
			values.add(numValue);
		}
		
		values.add(numValue);
		
		ResultSet result = simpleSearchValues(rows, values, "dzialy");
		ArrayList<Department> res = new ArrayList<Department>();
		while (result.next()) {
			res.add(new Department(
					result.getString("nazwa"),
					result.getString("id_dzialu"),
					result.getString("nr_regalu")));
		}
		
		result.close();
		
		return res;
		
	}
	
	
	public void removeElement(
			String id, String nameOfTable, String col) throws SQLException{
		Statement stmt = this.connection.createStatement();
		int val = stmt
				.executeUpdate("DELETE from " +
						nameOfTable + " where (" + col +" = "+ id + ")" );
		connection.commit();
		stmt.close();
		
	}
	
	protected boolean isExists(ArrayList<String>  cols,
			ArrayList<String> values,
			String nameOfTable) throws SQLException {
		
		String whereCondition = ""; 
		for (Integer i = 0; i < cols.size(); i++) {
			if (i < values.size() && (values.get(i) != null)) {
				if (i > 0) {
					whereCondition += " and ";
				}
				String value = values.get(i);
				whereCondition += ("( lower(substr(" + cols.get(i) +
						", 0, " + value.length() + ")) = lower('" + value + "' ))" );
				
			}
		}
		if (whereCondition.length() > 0) {
			whereCondition = " where " + whereCondition;
		}
		System.out.println("where condition:");
		System.out.println(whereCondition);
		
		
		Statement stmt = this.connection.createStatement();
		ResultSet res = stmt
				.executeQuery("select count(*) as num from " + nameOfTable + whereCondition );
		res.next();
		Integer size = res.getInt("num");
		res.close();
		stmt.close();
		if (size > 0) {
			return true;
		} else {
			return false;
		}
	}
	
	
}
